﻿CREATE PROCEDURE [dbo].[select_deal_month]
	@in_year int,
	@in_month int
AS
	declare @store money;
	declare @customer nvarchar(20);
	declare @code nvarchar(20);
	declare @fee nvarchar(20);
	declare @price money;
		
	declare @result_table table(result_id bigint identity(1,1) primary key,
								result_customer nvarchar(20) not null,
								result_fee nvarchar(20) not null,
								result_price money default(0) not null);
	declare deal_cursor cursor for
	select deal_customer, deal_code from tb_deal;
	open deal_cursor;
	fetch next from cust_cursor into @customer;
	while @@fetch_status = 0 begin
	
		select @store = isnull(sum(deal_store),0) from tb_deal
		where deal_customer = @customer and year(deal_date) = @in_year and
		month(deal_date) = @in_month;

		insert into @result_table(result_customer, result_fee, result_price)
		values(@customer,'仓储费',@store);
		
		declare code_cursor cursor for
		select distinct deal_code from tb_deal, tb_others where
		deal_code = others_code and deal_customer = others_customer;
		open code_cursor;
		fetch next from code_cursor into @code;
		while @@fetch_status = 0 begin

			declare fee_cursor cursor for
			select others_name, others_price from tb_others
			where others_customer = @customer and others_code = @code;
			open fee_cursor;
			fetch next from fee_cursor into @fee, @price;
			while @@fetch_status = 0 begin
				
				insert into @result_table(result_customer, result_fee, result_price)
				values(@customer,@fee,@price);
				
				fetch next from fee_cursor into @fee, @price;
			end
			close fee_cursor;
			deallocate fee_cursor;
		
			fetch next from code_cursor into @code;
		end
		close code_cursor;
		deallocate code_cursor;

		fetch next from customer_cursor into @customer;
	end
	close customer_cursor;
	deallocate customer_cursor;
	
	select * from @result_table;